﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text.RegularExpressions;
using DbUp.Engine;
using DbUp.Engine.Output;
using DbUp.Engine.Preprocessors;
using DbUp.Helpers;

namespace DbUp.PgSql
{
   /// <summary>
   /// Script executor for PostgreSQL databases.
   /// </summary>
   public class PgSqlScriptExecutor : IScriptExecutor
   {
      private readonly Func<IDbConnection> connectionFactory;
      private readonly Func<IUpgradeLog> log;
      private readonly IEnumerable<IScriptPreprocessor> scriptPreprocessors;

      /// <summary>
      /// Command Timeout in seconds. If not set, the default SQLCommand timeout is not changed.
      /// </summary>
      public int? ExecutionTimeoutSeconds { get; set; }

      /// <summary>
      /// Initializes an instance of the <see cref="PgSqlScriptExecutor"/> class.
      /// </summary>
      /// <param name="connectionFactory">The connection factory.</param>
      /// <param name="log">The logging mechanism.</param>
      /// <param name="schema">The schema that contains the table.</param>
      /// <param name="scriptPreprocessors">Script Preprocessors in addition to variable substitution</param>
      public PgSqlScriptExecutor( Func<IDbConnection> connectionFactory, Func<IUpgradeLog> log, string schema, IEnumerable<IScriptPreprocessor> scriptPreprocessors )
      {
         this.connectionFactory = connectionFactory;
         this.log = log;
         Schema = schema;
         this.scriptPreprocessors = scriptPreprocessors;
      }

      /// <summary>
      /// Database Schema, should be null if database does not support schemas
      /// </summary>
      public string Schema { get; set; }

      private static IEnumerable<string> SplitBySeparator( string script )
      {
         var scriptStatements =
             Regex.Split( script, @";\s*\n", RegexOptions.IgnoreCase | RegexOptions.Multiline )
                 .Select( x => x.Trim() )
                 .Where( x => x.Length > 0 )
                 .ToArray();

         return scriptStatements;
      }

      /// <summary>
      /// Executes the specified script against a database at a given connection string.
      /// </summary>
      /// <param name="script">The script.</param>
      public void Execute( SqlScript script )
      {
         Execute( script, null );
      }

      /// <summary>
      /// Verifies the existence of targeted schema. If schema is not verified, will check for the existence of the dbo schema.
      /// </summary>
      public void VerifySchema()
      {
         if ( string.IsNullOrEmpty( Schema ) )
            return;

         var sqlRunner = new AdHocSqlRunner( connectionFactory, Schema );

         if ( Convert.ToInt32( sqlRunner.ExecuteScalar( string.Format( @"select count(*) from pg_catalog.pg_namespace WHERE nspname = '{0}'", Schema ) ) ) == 0 )
         {
            sqlRunner.ExecuteNonQuery( string.Format( "CREATE SCHEMA '{0}';", Schema ) );
         }
      }

      /// <summary>
      /// Executes the specified script against a database at a given connection string.
      /// </summary>
      /// <param name="script">The script.</param>
      /// <param name="variables">Variables to replace in the script</param>
      public void Execute( SqlScript script, IDictionary<string, string> variables )
      {
         if ( variables == null )
            variables = new Dictionary<string, string>();
         if ( Schema != null && !variables.ContainsKey( "schema" ) )
            variables.Add( "schema", Schema );

         log().WriteInformation( "Executing SQL Server script '{0}'", script.Name );

         var contents = script.Contents;
         if ( string.IsNullOrEmpty( Schema ) )
            contents = new StripSchemaPreprocessor().Process( contents );
         contents = new VariableSubstitutionPreprocessor( variables ).Process( contents );
         contents = ( scriptPreprocessors ?? new IScriptPreprocessor[0] )
             .Aggregate( contents, ( current, additionalScriptPreprocessor ) => additionalScriptPreprocessor.Process( current ) );

         var scriptStatements = SplitBySeparator( contents );
         var index = -1;
         try
         {
            using ( var connection = connectionFactory() )
            {
               connection.Open();

               foreach ( var statement in scriptStatements )
               {
                  index++;
                  var command = connection.CreateCommand();
                  command.CommandText = statement;
                  if ( ExecutionTimeoutSeconds != null )
                     command.CommandTimeout = ExecutionTimeoutSeconds.Value;
                  command.ExecuteNonQuery();
               }
            }
         }
         catch ( SqlException sqlException )
         {
            log().WriteInformation( "SQL exception has occured in script: '{0}'", script.Name );
            log().WriteError( "Script block number: {0}; Block line {1}; Message: {2}", index, sqlException.LineNumber, sqlException.Procedure, sqlException.Number, sqlException.Message );
            log().WriteError( sqlException.ToString() );
            throw;
         }
         catch ( DbException sqlException )
         {
            log().WriteInformation( "DB exception has occured in script: '{0}'", script.Name );
            log().WriteError( "Script block number: {0}; Error code {1}; Message: {2}", index, sqlException.ErrorCode, sqlException.Message );
            log().WriteError( sqlException.ToString() );
            throw;
         }
         catch ( Exception ex )
         {
            log().WriteInformation( "Exception has occured in script: '{0}'", script.Name );
            log().WriteError( ex.ToString() );
            throw;
         }
      }
   }
}
